// GBaseExportTool.java
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.time.LocalDateTime;

/**
 * GBase 结构与数据导出工具
 * 运行前改好 url / user / pwd / exportDir
 */
public class GBaseExportTool {

    /* ====== 连接信息 ====== */
    private static final String URL = "jdbc:gbasedbt-sqli://192.168.1.6:9088/self_owned:GBASEDBTSERVER=gbase01";
    private static final String USER = "gbasedbt";
    private static final String PASSWORD = "GBase@123";

    /* ====== 导出目录 ====== */
    private static final String EXPORT_DIR = System.getProperty("user.dir") + "/gbase_dump/self_owned/";
    private static final String DDL_FILE = EXPORT_DIR + "schema.sql";
    private static final String DATA_FILE = EXPORT_DIR + "data.sql";
    private static final String TRUNCATE_FILE = EXPORT_DIR + "truncate.sql"; // 新增清空数据脚本文件

    /* ====== 主方法 ====== */
    public static void main(String[] args) throws Exception {
        File dir = new File(EXPORT_DIR);
        if (!dir.exists()) dir.mkdirs();

        Class.forName("com.gbasedbt.jdbc.Driver");
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            System.out.println("Connected: " + conn.getMetaData().getDatabaseProductName());

            exportSchema(conn);
            exportData(conn);
            exportTruncateScript(conn); // 新增导出清空数据脚本
        }
        System.out.println("Done! check " + EXPORT_DIR);
    }

    /* 1. 导出所有表结构 */
    private static void exportSchema(Connection conn) throws SQLException, IOException {
        try (PreparedStatement ps = conn.prepareStatement(
                "select table_name from user_tables");
             ResultSet rs = ps.executeQuery();
             PrintWriter pw = new PrintWriter(new OutputStreamWriter(new FileOutputStream(DDL_FILE),
                     StandardCharsets.UTF_8))) {

            pw.println("-- GBase schema dump " + LocalDateTime.now());
            while (rs.next()) {
                String table = rs.getString(1);
                pw.println("\n-- -------- " + table + " --------");
                pw.println(getCreateSQL(conn, table));
            }
        }
    }

    /* 获取单张表的 CREATE 语句 */
    private static String getCreateSQL(Connection conn, String table) throws SQLException {
        StringBuilder ddl = new StringBuilder("CREATE TABLE ").append(table).append(" (\n");

        String sql = "select column_name, data_type, data_length, cast(data_precision  as int) as data_precision, " +
                "data_scale, column_id, data_default from " +
                "user_tab_cols where table_name = ? order by column_id";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, table);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String colName = rs.getString("column_name");
                    String dataType = rs.getString("data_type");
                    String len = rs.getString("data_length");
                    String prec = rs.getString("data_precision");
                    String scale = rs.getString("data_scale");
                    String defaultVal = rs.getString("data_default");

                    ddl.append("  ").append(colName).append(" ").append(dataType);
                    if ("varchar".equalsIgnoreCase(dataType) && len != null) {
                        ddl.append("(").append(len).append(")");
                    } else if ("decimal".equalsIgnoreCase(dataType) && prec != null) {
                        ddl.append("(").append(prec).append(",").append(scale).append(")");
                    }

                    if (defaultVal != null && defaultVal.length() > 0) {
                        ddl.append(" DEFAULT ").append(defaultVal);
                    }
                    ddl.append(",\n");
                }
            }
        }

        // 去掉最后一个逗号换行
        if (ddl.charAt(ddl.length() - 2) == ',') {
            ddl.setLength(ddl.length() - 2);
        }
        ddl.append("\n);");
        return ddl.toString();
    }

    /* 2. 导出所有数据（INSERT） */
    private static void exportData(Connection conn) throws SQLException, IOException {
        try (PreparedStatement ps = conn.prepareStatement(
                "select table_name from user_tables");
             ResultSet rs = ps.executeQuery();
             PrintWriter pw = new PrintWriter(new OutputStreamWriter(new FileOutputStream(DATA_FILE),
                     StandardCharsets.UTF_8))) {

            pw.println("-- GBase data dump " + LocalDateTime.now());
            while (rs.next()) {
                String table = rs.getString(1);
                System.out.println("Exporting data : " + table);
                exportTableData(conn, table, pw);
            }
        }
    }

    /* 导出单表数据 */
    private static void exportTableData(Connection conn, String table, PrintWriter pw) throws SQLException {
        String sql = "SELECT * FROM " + table + "";
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery(sql)) {

            ResultSetMetaData md = rs.getMetaData();
            int col = md.getColumnCount();

            while (rs.next()) {
                StringBuilder sb = new StringBuilder("INSERT INTO ")
                        .append(table).append(" VALUES (");
                for (int i = 1; i <= col; i++) {
                    Object val = rs.getObject(i);
                    if (val == null) {
                        sb.append("NULL");
                    } else if (val instanceof Number || val instanceof Boolean) {
                        sb.append(val);
                    } else {
                        // 字符串/日期/时间戳 统一单引号转义
                        sb.append('\'')
                                .append(val.toString().replace("'", "''"))
                                .append('\'');
                    }
                    if (i < col) sb.append(',');
                }
                sb.append(");");
                pw.println(sb);
            }
        }
    }

    /* 3. 导出清空所有表数据的脚本 */
    private static void exportTruncateScript(Connection conn) throws SQLException, IOException {
        try (PreparedStatement ps = conn.prepareStatement(
                "select table_name from user_tables");
             ResultSet rs = ps.executeQuery();
             PrintWriter pw = new PrintWriter(new OutputStreamWriter(new FileOutputStream(TRUNCATE_FILE),
                     StandardCharsets.UTF_8))) {

            pw.println("-- GBase truncate all tables script " + LocalDateTime.now());

            // 收集所有表名
            java.util.List<String> tableNames = new java.util.ArrayList<>();
            while (rs.next()) {
                tableNames.add(rs.getString(1));
            }

            // 生成TRUNCATE语句
            for (String table : tableNames) {
                pw.println("TRUNCATE TABLE " + table + ";");
            }

        }
    }
}
